﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;
using MySql.Data.MySqlClient;


public class UsuarioDAO
{
    MySqlConnection connection = Database.getInstance.getConnection();

    public UsuarioDAO()
    {

    }

    public List<UsuarioDTO> getAll()
    {
        try
        {
            MySqlCommand command = connection.CreateCommand();
            MySqlDataReader reader;

            command.CommandText = "SELECT * FROM usuario";
            connection.Open();

            List<UsuarioDTO> Usuario_List = new List<UsuarioDTO>();

            reader = command.ExecuteReader();
            while (reader.Read())
            {
                UsuarioDTO usuario = new UsuarioDTO();
                usuario.Idusuario = Convert.ToInt32(reader["idusuario"]);
                usuario.Nome = (string)(reader["nome"]);
                usuario.Email = (string)(reader["email"]);
                usuario.Senha = (string)(reader["senha"]);

                Usuario_List.Add(usuario);
            }
            connection.Close();
            return Usuario_List;
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }

    public List<UsuarioGerenteDTO> getAllGerentes()
    {
        try
        {
            MySqlCommand command = connection.CreateCommand();
            MySqlDataReader reader;

            command.CommandText = "select * from `thiagocarvalhal7`.`gerente` inner join `thiagocarvalhal7`.`usuario` on `thiagocarvalhal7`.`gerente`.`usuario_idusuario` = `thiagocarvalhal7`.`usuario`.`idusuario`";
            connection.Open();

            List<UsuarioGerenteDTO> UsuarioGerente_List = new List<UsuarioGerenteDTO>();

            reader = command.ExecuteReader();
            while (reader.Read())
            {
                UsuarioGerenteDTO usuarioGerente = new UsuarioGerenteDTO();
                usuarioGerente.Idusuario = Convert.ToInt32(reader["idusuario"]);
                usuarioGerente.Nome = (string)(reader["nome"]);
                usuarioGerente.Email = (string)(reader["email"]);
                usuarioGerente.Senha = (string)(reader["senha"]);
                usuarioGerente.Idgerente = Convert.ToInt32(reader["idgerente"]);
                usuarioGerente.Cpf = (string)(reader["cpf"]);
                usuarioGerente.Telefone = (string)(reader["telefone"]);              

                UsuarioGerente_List.Add(usuarioGerente);
            }
            connection.Close();
            return UsuarioGerente_List;
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }


    public int getIdUsuarioByEmail(String email)
    {
        try
        {
            int id = 0;

            MySqlCommand command = connection.CreateCommand();
            MySqlDataReader reader;

            command.CommandText = "SELECT idusuario FROM usuario WHERE email=@email LIMIT 1";
            command.Parameters.AddWithValue("@email", email);
            connection.Open();

            List<UsuarioDTO> Usuario_List = new List<UsuarioDTO>();

            reader = command.ExecuteReader();
            while (reader.Read())
            {               
                id = Convert.ToInt32(reader["idusuario"]);               
            }
            connection.Close();
            return id;
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }

    public bool insere(UsuarioDTO usuario)
    {
        try
        {
            MySqlCommand command = connection.CreateCommand();          

            command.CommandText = "INSERT INTO usuario (nome, email, senha) VALUES (@nome, @email, @senha)";
            connection.Open();

            command.Parameters.AddWithValue("@nome", usuario.Nome);
            command.Parameters.AddWithValue("@email", usuario.Email);
            command.Parameters.AddWithValue("@senha", usuario.Senha);
            command.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (SqlException ex)
        {
            return false;
        }
    }

    public bool deleta(int idusuario)
    {
        try
        {
            MySqlCommand command = connection.CreateCommand();

            command.CommandText = "DELETE FROM usuario WHERE idusuario=@idusuario";
            connection.Open();
          
            command.Parameters.AddWithValue("@idusuario", idusuario);

            command.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public bool atualiza(UsuarioDTO usuario)
    {

        try
        {

            MySqlCommand command = connection.CreateCommand();

            command.CommandText = "UPDATE usuario SET nome=@nome, email=@email, senha=@senha WHERE idusuario=@idusuario";
            connection.Open();

            command.Parameters.AddWithValue("@nome", usuario.Nome);
            command.Parameters.AddWithValue("@nota1", usuario.Email);
            command.Parameters.AddWithValue("@nota2", usuario.Senha);
            command.Parameters.AddWithValue("@id", usuario.Idusuario);

            command.ExecuteNonQuery();
            connection.Close();
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }

    }


}
